//
//  SqliteManager.m
//  HappyBaby
//
//  Created by 华哥 on 15/11/25.
//  Copyright © 2015年 华哥. All rights reserved.
//

#import "SqliteManager.h"

static sqlite3 *db = nil;
@implementation SqliteManager

+ (SqliteManager *)shareInstance
{
    static SqliteManager *sql = nil;
    if (sql == nil)
    {
        sql = [[SqliteManager alloc]init];
    }
    return sql;
}

- (sqlite3 *)openDB
{
    // 需要一个sqlite3的指针, 一会用于连接咱们在沙盒中创建的数据库; 相当于用于保存数据库的地址
    // 先指向nil, 因为此时我们还没有创建数据库;
    //    sqlite3 *db = nil;
    if (db != nil)
    {
        return db;
    }
    NSString *filePath = [self documentspathWithSqliteName:@"HappyBaby.sqlite"];
    sqlite3_open([filePath UTF8String], &db);
    NSLog(@"%@",filePath);
    return db;
}
// 直接获得我们沙盒中document数据库的路径
- (NSString *)documentspathWithSqliteName:(NSString *)sqliteName
{
    return [[NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject]stringByAppendingPathComponent:sqliteName];
}
- (void)closeDB
{
    sqlite3_close(db);
    db = nil;
}

#pragma mark --- 在数据库中创建表 ---
- (void)creatTableWithTableName:(NSString *)tableName
{
    // 1. 打开数据库
    db = [self openDB];
    // 2. 创建指令集, 这个指令集, 可以理解成一个存储信息的集合, 用于存储我们所有的sql语句信息;
    sqlite3_stmt *stmt = nil;
    // 3. 创建sql语句,
    NSString *string = [NSString stringWithFormat:@"create table if not exists %@ (ID integer primary key autoincrement, artist text, pic text, downurl text, duration float, filesize double, myID text, name text, playcnt float, savePath text)", tableName];
    
    NSInteger flag = sqlite3_prepare_v2(db, [string UTF8String], -1, &stmt, nil);
    NSLog(@"创建表flag == %ld", flag);
    if (SQLITE_OK == flag)
    {
        NSLog(@"创建表成功");
        sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    [self closeDB];
}

#pragma mark --- 在特定表名中插入数据 ---
- (void)insertDataWithTableName:(NSString *)tableName childrenStoryDetail:(ChildrenStoryInterface *)model
{
    db = [self openDB];
    sqlite3_stmt *stmt = nil;
    NSString *string = [NSString stringWithFormat:@"insert into %@ (artist, pic, downurl, duration, filesize, myID, name, playcnt, savePath) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", tableName];
     NSInteger flag = sqlite3_prepare_v2(db, [string UTF8String], -1, &stmt, nil);
    NSLog(@"%ld",(long)flag);
    if (SQLITE_OK == flag)
    {
        sqlite3_bind_text(stmt, 1, [model.artist UTF8String], -1, nil);
        sqlite3_bind_text(stmt, 2, [model.pic UTF8String], -1, nil);
        sqlite3_bind_text(stmt, 3, [model.downurl UTF8String], -1, nil);
        sqlite3_bind_double(stmt, 4, model.duration);
        sqlite3_bind_double(stmt, 5, model.filesize);
        sqlite3_bind_text(stmt, 6, [model.myID UTF8String], -1, nil);
        sqlite3_bind_text(stmt, 7, [model.name UTF8String], -1, nil);
        sqlite3_bind_int(stmt, 8, (int)model.playcnt);
        sqlite3_bind_text(stmt, 9, [model.savePath UTF8String], -1, nil);
        
        sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    [self closeDB];
}

- (void)deleteDataWithTableName:(NSString *)tableName downurl:(NSString *)url;
{
    // 1. 打开数据库
    db = [self openDB];
    // 2. 创建指令集
    sqlite3_stmt *stmt = nil;
    // 3. 创建sql语句
    NSString *string = [NSString stringWithFormat:@"delete from %@ where downurl = ?", tableName];
    
    // 4. 创建语句对象
    NSInteger flag = sqlite3_prepare_v2(db, [string UTF8String], -1, &stmt, nil);
    if (SQLITE_OK == flag)
    {
        // 绑定myID
//        sqlite3_bind_int(stmt, 1, (int)myID);
        sqlite3_bind_text(stmt, 1, [url UTF8String], -1, nil);
        // 执行指令集
        sqlite3_step(stmt);
        NSLog(@"删除数据成功");
    }
    // 释放指令集
    sqlite3_finalize(stmt);
    // 关闭数据库
    [self closeDB];
}

- (ChildrenStoryInterface *)selectOneDataWithTableName:(NSString *)tableName downurl:(NSString *)url
{
    // 1. 打开数据库
    db = [self openDB];
    // 2. 创建指令集
    sqlite3_stmt *stmt = nil;
    // 3. 创建sql语句
    NSString *string = [NSString stringWithFormat:@"select * from %@ where downurl = ?", tableName];
    // 4. 创建语句对象
    NSInteger flag = sqlite3_prepare_v2(db, [string UTF8String], -1, &stmt, nil);
    

    ChildrenStoryInterface *model = nil;
    if (SQLITE_OK == flag)
    {
        sqlite3_bind_text(stmt, 1, [url UTF8String], -1, nil);

        while (SQLITE_ROW == sqlite3_step(stmt))
        {
            model = [[ChildrenStoryInterface alloc]init];
            
            const unsigned char *artist = sqlite3_column_text(stmt, 1);
            model.artist = [NSString stringWithUTF8String:(const char *)artist];
            
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            model.pic = [NSString stringWithUTF8String:(const char *)pic];
            
            const unsigned char *downurl = sqlite3_column_text(stmt, 3);
            model.downurl = [NSString stringWithUTF8String:(const char *)downurl];
            
            double duration = sqlite3_column_double(stmt, 4);
            model.duration = duration;
            
            double filesize = sqlite3_column_double(stmt, 5);
            model.filesize = filesize;
            
            const unsigned char *myID = sqlite3_column_text(stmt, 6);
            model.myID = [NSString stringWithUTF8String:(const char *)myID];
            
            const unsigned char *name = sqlite3_column_text(stmt, 7);
            model.name = [NSString stringWithUTF8String:(const char *)name];
            
            double playcnt = sqlite3_column_double(stmt, 8);
            model.playcnt = playcnt;
            
            const unsigned char *savePath = sqlite3_column_text(stmt, 9);
            model.savePath = [NSString stringWithUTF8String:(const char *)savePath];
        }
    }
    sqlite3_finalize(stmt);
    [self closeDB];
    return model;
}

#pragma mark --- 在特定表名中查询所有数据 ---
- (NSMutableArray *)selectAllDataWithTableName:(NSString *)tableName
{
    db = [self openDB];
    
    sqlite3_stmt *stmt = nil;
    
    NSString *string = [NSString stringWithFormat:@"select * from %@", tableName];
    
    NSInteger flag = sqlite3_prepare(db, [string UTF8String], -1, &stmt, nil);
    
    NSMutableArray *modelArray = [NSMutableArray array];
    if (SQLITE_OK == flag)
    {
        
        while (SQLITE_ROW == sqlite3_step(stmt))
        {
            ChildrenStoryInterface *model = [[ChildrenStoryInterface alloc]init];
            
            const unsigned char *artist = sqlite3_column_text(stmt, 1);
            model.artist = [NSString stringWithUTF8String:(const char *)artist];
            
            const unsigned char *pic = sqlite3_column_text(stmt, 1);
            model.artist = [NSString stringWithUTF8String:(const char *)pic];
            
            const unsigned char *downurl = sqlite3_column_text(stmt, 3);
            model.downurl = [NSString stringWithUTF8String:(const char *)downurl];
            
            double duration = sqlite3_column_double(stmt, 4);
            model.duration = duration;
            
            double filesize = sqlite3_column_double(stmt, 5);
            model.filesize = filesize;
            
            const unsigned char *myID = sqlite3_column_text(stmt, 6);
            model.myID = [NSString stringWithUTF8String:(const char *)myID];
            
            const unsigned char *name = sqlite3_column_text(stmt, 7);
            model.name = [NSString stringWithUTF8String:(const char *)name];
            
            double playcnt = sqlite3_column_double(stmt, 8);
            model.playcnt = playcnt;
            
            const unsigned char *savePath = sqlite3_column_text(stmt, 9);
            model.savePath = [NSString stringWithUTF8String:(const char *)savePath];
            
            [modelArray addObject:model];
        }
    }
    sqlite3_finalize(stmt);
    [self closeDB];
    return modelArray;
}








@end
